package com.System.LearnSystem;
import java.sql.*;
public class MYSQL {//建立数据库
    public static void main(String[] args) {
        String url = "jdbc:mysql://127.0.0.1:3306";
        String username = "root";
        String password = "1234";
        try (Connection conn = DriverManager.getConnection(url, username, password);
            Statement statement = conn.createStatement()) {
            statement.executeUpdate("CREATE DATABASE IF NOT EXISTS user");
            statement.executeUpdate("USE user;"); 
            //Usertable用于设置建表语句
            String Usertable = "CREATE TABLE IF NOT EXISTS `t_user` (" //创建用户表 
            		  +"`id` bigint NOT NULL AUTO_INCREMENT ,"    //主键ID
            		  +"`username` varchar(50) NOT NULL ,"        //用户名
            		  +"`password` varchar(100) NOT NULL ,"	      //密码
            		  +"`role` varchar(20) NOT NULL  ,"		      //角色(ADMIN/TEACHER/STUDENT)
            		  +"`status` tinyint NOT NULL DEFAULT '0',"   //状态(0-正常,1-禁用)
            		  +"`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ,"  //创建时间
            		  +"`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,"//更新时间
            		  +"PRIMARY KEY (`id`),"
            		  +"UNIQUE KEY `uk_username` (`username`)"
            		  +") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;";
            statement.executeUpdate(Usertable);
            System.out.println("用户表创建成功");
            
            Usertable ="CREATE TABLE IF NOT EXISTS `t_teacher`("//创建教师表
            			+"`id` bigint NOT NULL AUTO_INCREMENT  , "//主键ID
            			+"`username` varchar(50) NOT NULL ,"      //用户名
            			+"`teacher_no` varchar(20) NOT NULL  ,"//教师工号
            			+"`department_id` bigint NOT NULL  ,"//院系ID
            			+"`name` varchar(50) NOT NULL  ,"//教师姓名
            			+"`phone` varchar(20) DEFAULT NULL  ,"//联系电话
            			+"`email` varchar(50) DEFAULT NULL  ,"//邮箱
            			+"`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ,"  //创建时间
              		    +"`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,"//更新时间
              		    +"PRIMARY KEY (`id`),"
              		    +"UNIQUE KEY `uk_teacher_no` (`teacher_no`),"
              		    +"KEY `idx_department_id` (`department_id`)"
              		    +")ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;";
            statement.executeUpdate(Usertable);
            System.out.println("教师表创建成功");
            
            Usertable ="CREATE TABLE IF NOT EXISTS `t_student`("//创建学生表
        			+"`id` bigint NOT NULL AUTO_INCREMENT  , "//主键ID
        			+"`username` varchar(50) NOT NULL ,"     //用户名
        			+"`student_no` varchar(20) NOT NULL  ,"//学号
        			+"`department_id` bigint NOT NULL  ,"//院系ID
        			+"`name` varchar(50) NOT NULL  ,"//学生姓名
        			+"`phone` varchar(20) DEFAULT NULL  ,"//联系电话
        			+"`email` varchar(50) DEFAULT NULL  ,"//邮箱
        			+"`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ,"  //创建时间
          		    +"`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,"//更新时间
          		    +"PRIMARY KEY (`id`),"
          		    +"UNIQUE KEY `uk_student_no` (`student_no`),"
          		    +"KEY `idx_department_id` (`department_id`)"
          		    +")ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;";
        statement.executeUpdate(Usertable);
        System.out.println("学生表创建成功");
        
        Usertable = "CREATE TABLE IF NOT EXISTS `t_course` ("
        		+ "  `id` bigint NOT NULL AUTO_INCREMENT, "  //主键ID
        		+ "  `course_no` varchar(20) NOT NULL, "		//课程编号
        		+ "  `course_name` varchar(100) NOT NULL,"  //课程名称
        		+ "  `teacher_id` bigint NOT NULL,"			//'授课教师ID
        		+ "  `major_id` bigint NOT NULL,"		//适用专业ID
        		+ "  `grade` tinyint NOT NULL,"			//适用年级
        		+ "  `course_type` tinyint NOT NULL,"		//课程性质(1-必修,2-选修)
        		+ "  `credit` decimal(3,1) NOT NULL,"	//学分
        		+ "  `is_public` tinyint NOT NULL DEFAULT '0',"//是否公开(0-否,1-是)
        		+ "  `status` tinyint NOT NULL,"//课程状态(1-已提交,2-审核通过,3-审核不通过,4-公开,5-隐藏)
        		+ "`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ,"  //创建时间
          		+ "`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,"//更新时间
        		+ "  PRIMARY KEY (`id`),"
        		+ "  UNIQUE KEY `uk_course_no` (`course_no`),"
        		+ "  KEY `idx_teacher_id` (`teacher_id`),"
        		+ "  KEY `idx_major_id` (`major_id`)"
        		+ ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
        statement.executeUpdate(Usertable);
        System.out.println("课程表创建成功");
        // 创建初始课程的语句（测试用）INSERT INTO t_course (course_no,course_name,teacher_id,major_id,grade,credit,status,course_type) VALUES ('101','软件工程','001','111','1','1','1','1');
      
        Usertable = "CREATE TABLE IF NOT EXISTS `student001` (" //初始学生的课程表
        		+ " `class` tinyint,"                          	//存储每天的课表
        		+ " `FIRST` varchar(100),"						//和我们的课表一一对应
        		+ " `Second` varchar(100),"						//比如FIRST即为第一大节
        		+ " `M`varchar(100),"
        		+ " `Third`varchar(100),"
        		+ " `N`varchar(100),"
        		+ " `Fourth`varchar(100),"
        		+ " `Fifth`varchar(100),"
        		+ "  UNIQUE KEY `uk_class` (`class`)"
        		+ ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
        statement.executeUpdate(Usertable);
        System.out.println("student001的课程表创建成功");
        
        Usertable = "INSERT INTO student001 (class)"
	        	+ "VALUES" 
	        	+"('1'),"
	        	+"('2'),"
	        	+"('3'),"
	        	+"('4'),"
	        	+"('5'),"
	        	+"('6'),"
	        	+"('7');";
        statement.executeUpdate(Usertable);
        System.out.println("student001的课程表初始化成功");
        //创建初始数据，用以进行后续的测试操作
            if(!checkUserExists("张三"))//在表中创建初始管理员
            {	
            	String User ="INSERT INTO t_user (username,password,role) VALUES ('张三','12345','ADMIN');";
            	statement.executeUpdate(User);
            }
            if(!checkUserExists("teacher001"))//在表中创建初始教师
            {
            	String User ="INSERT INTO t_user (username,password,role) VALUES ('teacher001','666','TEACHER');";
            	statement.executeUpdate(User);
            	User = "INSERT INTO t_teacher(username,name,teacher_no,department_id) VALUES ('teacher001','慧','23001001','001');";
            	statement.executeUpdate(User);
            }
            if(!checkUserExists("student001"))//在表中创建初始学生
            {
            	String User ="INSERT INTO t_user (username,password,role) VALUES ('student001','777','STUDENT');";
            	statement.executeUpdate(User);
            	User = "INSERT INTO t_student(username,name,student_no,department_id) VALUES ('student001','利泽特','23001001','001');";
            	statement.executeUpdate(User);
            }
            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
       
    public static boolean checkUserExists(String n) {//检查表中有无成员n
    	String url = "jdbc:mysql://127.0.0.1:3306/user";
        String username = "root";
        String password = "1234";
        String sql = "SELECT * FROM t_user WHERE username = ?";
        try (Connection conn = DriverManager.getConnection(url, username, password);
        		PreparedStatement pstmt = conn.prepareStatement(sql)) 
        {
            pstmt.setString(1, n); 
            ResultSet rs = pstmt.executeQuery();
            return rs.next();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    
    public static boolean checkteaNoExists(String n) {//检查表中有无教师编号n
    	String url = "jdbc:mysql://127.0.0.1:3306/user";
        String username = "root";
        String password = "1234";
        String sql = "SELECT * FROM t_teacher WHERE teacher_no = ?";
        try (Connection conn = DriverManager.getConnection(url, username, password);
        		PreparedStatement pstmt = conn.prepareStatement(sql)) 
        {
            pstmt.setString(1, n); 
            ResultSet rs = pstmt.executeQuery();
            return rs.next();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    
    public static boolean checkstuNoExists(String n) {//检查表中有无学生编号n
    	String url = "jdbc:mysql://127.0.0.1:3306/user";
        String username = "root";
        String password = "1234";
        String sql = "SELECT * FROM t_teacher WHERE student_no = ?";
        try (Connection conn = DriverManager.getConnection(url, username, password);
        		PreparedStatement pstmt = conn.prepareStatement(sql)) 
        {
            pstmt.setString(1, n); 
            ResultSet rs = pstmt.executeQuery();
            return rs.next();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
}

